﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[tblWH_Perfmon]
#-- Purpose:		Store the warehouse copy of perfmon activity
#--	Last Update:	02/27/2014
#--					For a complete history - please review comments in Version
#--					Control.
#------------------------------------------------------------------------------
*/
CREATE TABLE [dbo].[tblWH_Perfmon] (
	perf_id INT IDENTITY(1, 1) NOT NULL,
	date_inserted DATETIME NOT NULL,
	[server_ssd_id] INT NOT NULL,
	buffer_cache_hit_ratio NUMERIC(5, 2) NULL,
	page_life_expectancy BIGINT NULL,
	logins_per_sec BIGINT NULL,
	logouts_per_sec BIGINT NULL,
	user_connections BIGINT NULL,
	optimizer_memory_KB BIGINT NULL,
	sql_cache_memory_KB BIGINT NULL,
	target_server_memory_KB BIGINT NULL,
	total_server_memory_KB BIGINT NULL,
	sql_compilations_per_sec BIGINT NULL,
	sql_recompilations_per_sec BIGINT NULL,
	batch_requests_per_sec BIGINT NULL,
	number_of_deadlocks_per_sec BIGINT NULL,
	average_wait_time_ms BIGINT NULL,
	lock_waits_per_sec BIGINT NULL,
	lock_wait_time_ms BIGINT NULL,
	full_scans_per_sec BIGINT NULL,
	lazy_writes_per_sec BIGINT NULL,
	page_reads_per_sec BIGINT NULL,
	page_writes_per_sec BIGINT NULL,
	checkpoint_pages_per_sec BIGINT NULL,
	page_splits_per_sec BIGINT NULL,
	transactions_per_sec BIGINT NULL,
	cpu_busy BIGINT NULL,
	io_busy BIGINT NULL,
	idle BIGINT NULL,
	[rownum] INT NULL, 
    CONSTRAINT [PK_tblWH_Perfmon] PRIMARY KEY CLUSTERED ([perf_id] ASC),
	CONSTRAINT [FK_tblWH_Perfmon_tblMSX_server_discovery] FOREIGN KEY ([server_ssd_id]) REFERENCES [tblMSX_server_discovery]([ssd_id])
	)
GO
CREATE NONCLUSTERED INDEX [IX_tblWH_Perfmon_ServerDate] ON [dbo].[tblWH_Perfmon]
(
	[date_inserted],
	[server_ssd_id]
) 
INCLUDE (
	[page_life_expectancy],
	[target_server_memory_KB]
)
GO
CREATE NONCLUSTERED INDEX [IX_tblWH_Perfmon_PLE] ON [dbo].[tblWH_Perfmon] 
(
	[server_ssd_id],
	[date_inserted],
	[rownum]
) 
INCLUDE
(
	[page_life_expectancy],
	[target_server_memory_KB]
)
GO
CREATE NONCLUSTERED INDEX [IX_tblWH_Perfmon_CPU] ON [dbo].[tblWH_Perfmon]
(
	[server_ssd_id] ASC
)
INCLUDE
(
	[date_inserted],
	[cpu_busy],
	[idle]
)
GO